﻿//////////////////////////////////////////////////////////
////################ GUI CALLS #######################////

// GUI API calls to redraw or build elements to be inserted
function html_to_sql(str) {
    // first replace the breaks with newlines
    str = str.replace(/<br\/?>/gi, '\n');
    // grab all remainer <tags> and remove them    
    var regex = /<\/?([A-Z][A-Z0-9]*)[^>]*>/gi
    str = str.replace(regex, '');
    str = str.replace(/&nbsp;/g, ' ');
    str = str.replace(/&gt;/g, '>');
    str = str.replace(/&lt;/g, '<');
    return str;
}

// precondition, the esc_sql_to_html should be ran first otherwise you'll show tags
function sql_to_html(str) {
    var regex;
    var singleQuoteToken = '_SQ_';
    // some syntax chars that should be translated
    str = str.replace(/</g, '&lt;').replace(/>/g, '&gt;');
    // replace all 'single quotes' inside a [sql field'] with a temp _SQ_ and we'll swap it later
    regex = /\[[^[\[]*]/g
    str = str.replace(regex, function (xstr) { return xstr.replace("'", singleQuoteToken) });
    // keywords
    regex = /\badd\b|\ball\b|\band\b|\bany\b|\bas\b|\basc\b|\bbetween |\bby\b|\bcase\b|\bconvert\b|\bcreate\b|\bcurrent\b|\bcurrent_date\b|\bcurrent_time\b|\bcurrent_timestamp\b|\bdelete\b|\bdesc\b|\bdistinct\b|\bdrop\b|\bdump\b|\belse\b|\bend\b|\bescape\b|\bexec\b|\bexists\b|\bfrom |\bgroup\b| identity\b|\bif\b|\bin |insert\b|\bintersect\b|\binto\b|\bis\b|\bjoin\b|\bkey\b|\bleft\b|\blike\b|\bnot\b|\bnull\b|\bnullif\b|\bof\b|\boff\b|\boffsets\b|\bon\b|\bor |\border\b| outer\b|\bpercent\b|\bright\b|\browcount\b|\bselect\b|\bset\b|\bsome\b|\bstatistics\b|\btable\b|\bthen\b|\bto\b|\btop\b|\bunion\b|\bunique\b|\bupdate\b|\buse\b|\bvalues\b|\bview\b|\bwhen\b|\bwhere\b|\bwhile\b|\bwith\b/gi
    str = str.replace(regex, colorize_blue);
    // functions
    // date and time functions
    regex = /(\bdateadd\b|\bdatediff\b|\bdatename\b|\bdatepart\b|\bday\b|\bgetdate\b|\bgetutcdate\b|\bmonth\b|\byear)\(.*?\)\b/gi
    str = str.replace(regex, colorize_violet);
    // math functions
    regex = /(\babs\b|\bacos\b|\basin\b|\batan\b|\batn2\b|\bceiling\b|\bcos\b|\bcot\b|\bdegrees\b|\bexp\b|\bfloor\b|\blog\b|\blog10\b|\bpi\b|\bpower\b|\bradians\b|\brand\b|\bround\b|\bsign\b|\bsin\b|\bsquare\b|\bsqrt\b|\btan\b)\(.*?\)/gi
    str = str.replace(regex, colorize_violet);
    // string functions
    regex = /(\bascii\b|\bchar\b|\bcharindex\b|\bdifference\b|\bleft\b|\blen\b|\blower\b|\bltrim\b|\bnchar\b|\bpatindex\b|\breplace\b|\bquotename\b|\breplicate\b|\breverse\b|\bright\b|\brtrim\b|\bsoundex\b|\bspace\b|\bstr\b|\bstuff\b|\bsubstring\b|\bunicode\b|\bupper\b)\(.*?\)/gi
    str = str.replace(regex, colorize_violet);
    // aggregate functions
    regex = /(\bavg\b|\bbinary_checksum\b|\bchecksum\b|\bchecksum_agg\b|\bcount\b|\bcount_big\b|\bgrouping\b|\bmax\b|\bmin\b|\bsum\b|\bstdev\b|\bstdevp\b|\bvar\b|\bvarp\b)\(.*?\)/gi
    str = str.replace(regex, colorize_violet);
    // user text
    regex = /\'.*[^\']*\'/gi
    str = str.replace(regex, colorize_red);
    // now that we replaced user text, replace the single quotes
    str = str.replace(singleQuoteToken, "'");
    // put page breaks in new lines
    str = str.replace(/\n/g, function (a) { return '<br/>' });      // keep the newline, even though it wont be shown
    // whenever there is a string of white spaces, replace it with a &nbsp;
    str = str.replace(/\s\s/g, function (a) { return '&nbsp;' });
    return str;
}

function colorize_blue(str) {
    return '<span style="color: blue">' + str + '</span>';
}

function colorize_red(str) {
    return '<span style="color: red">' + str + '</span>';
}

function colorize_violet(str) {
    // colorize a function, do the func( ), but not the inside
    var indexstart = str.indexOf('(') + 1;
    var indexend = str.lastIndexOf(')');
    return '<span style="color: #ee82ee">' + str.substring(0, indexstart) + '</span>' +
        str.substring(indexstart, indexend) +
        '<span style="color: #ee82ee">' + str.substring(indexend, indexend + 1) + '</span>';
}

///////////////////////////////////////////////////////////////
//#################  QUERY AND TABLE STRUCTS ###############///

// note this c# tag evaluates even though intellisense does not work
function onLoad() {
    reselectList();
}


function loadQueryDiv() {
    var query = getQuery(getSelectedQueryKey());
    if (query != null) {
        $id('sqlDiv').innerHTML = sql_to_html(query.sql_code);
        $id('inputAltQuery').disabled = false;
        $id('inputCopyToSql').disabled = false;
    }
}

// used on start up so the user's selected queries are the same from previous page, alsu used when we rebuild the list via ajax
function reselectList() {
    var select = $id('querySelectList');
    var curItem = $id('create_query_short_desc_input').value;

    for (var i = 0; i < select.options.length; i++) {
        var option = select.options[i];
        if (option.text == curItem) {
            select.selectedIndex = i;
            var sqlVar = getQuery(option.value);
            break;
        }
    }

    if (select.selectedIndex >= 0) {
        loadQueryDiv();
    }
}

function repopulateQueryList() {
    var selectE = $id('querySelectList');
    var currentShortDesc = $id('create_query_short_desc_input').value;
    // clear list
    selectE.innerHTML = "";

    /// rebuild list
    for (var x in _jsonQueries) {
        var can = _jsonQueries[x];
        var opt = document.createElement("option");
        var shortDescTrim = can.short_desc;
        opt.text = shortDescTrim;
        opt.value = can.short_desc;
        if (can.short_desc == currentShortDesc) {
            opt.selected = true;
        }
        selectE.add(opt, null);
    }
}

// API To get JSON objects via ids
function getSelectedQueryKey() {
    return getSelectedItem($id('querySelectList'));
}

function enableQueryEditor(enabled) {
    $id('query_ispublic').disabled = !enabled;    
    $id('btnSaveQuery').disabled = !enabled;
    $id('btnDeleteQuery').disabled = !enabled;
}

/////////////////////////////////////
// event handlers for browser click functions
//////////////////////////////////
function clickShowCreateDesc(visible) {
    $id('create_query_div').style.display = (visible ? "block" : "none");
    $id('select_query_div').style.display = (!visible ? "block" : "none");
}

function clickLoadQuery() {
    var query = getQuery(getSelectedQueryKey());
    if (query != null) {
        $id('create_query_short_desc_input').value = query.short_desc;
        $id('create_query_long_desc_input').value = query.long_desc;
        $id('query_ispublic').checked = query.is_public;
        loadQueryDiv();

        // set enable proprties if we own this item
        var owned = query.owner == _jsonUser.UserName;
        enableQueryEditor(owned);
    }
}

function typedShortDescModified() {
    // if items were locked because last item did not belong to user, open access on the fields
    // to determine that state, we look at if the public checkbox is enabled or not
    if ($id('query_ispublic').disabled) {
        $id('query_ispublic').checked = false;
        enableQueryEditor(true);
    }
}

function clickCopyToSQL(executeQuery) {
    $id('sqlTextBox').value = html_to_sql($id('sqlDiv').innerHTML);
    if (executeQuery) {
        $id('aspnetForm').submit();
    }
}

function clickSaveToFile() {
    // couple tricks here, we change our webpage to sqloutput.aspx and append the URL params
    var sql_code = $id('sqlTextBox').value;
    var output_type = "csv";

    var url = location.href;
    url = url.substr(0, url.lastIndexOf('/')) + '/sqloutput.aspx?';
    url += "output_type=" + output_type;
    url += "&sql_code=" + escape(sql_code);

    location.href = url;
}

function clickSaveToView() {
    // couple tricks here, we change our webpage to sqloutput.aspx and append the URL params
    var sql_code = $id('sqlTextBox').value;

    var url = location.href;
    url = url.substr(0, url.lastIndexOf('/')) + '/ViewDesigner.aspx?';
    url += "view_name=" + escape($id('create_query_short_desc_input').value.trim());
    url += "&sql_query=" + escape(sql_code);

    location.href = url;

}

function clickSaveQuery() {
    var shortDesc = $id('create_query_short_desc_input').value.trim();
    var longDesc = $id('create_query_long_desc_input').value;
    var sqlCode = $id('sqlTextBox').value;
    var ispublic = $id('query_ispublic').checked;

    if (shortDesc.length < 1) {
        alert("unable to save query, short description invalid");
    }
    if (shortDesc.length > 50) {
        alert("unable to save uery, short description too long (50 char max)");
    }

    ajaxSaveQuery(shortDesc, longDesc, sqlCode, ispublic,
    function (json) {
        repopulateQueryList();
        loadQueryDiv();
    });
}

function clickDeleteQuery() {
    var shortDesc = $id('create_query_short_desc_input').value;
    ajaxDeleteQuery(shortDesc, function (suc) {
        if (suc) {
            repopulateSqlList();
        }
    });
}

// call helper method in master.js
addLoadEvent(onLoad);